#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
use cy_database;

with
tmp_business_alive1 as
(
	select
		name,
		date_format(entry_work,'yyyy-MM') as ew_time,
		date_format(out_work,'yyyy-MM') as ow_time,
		ent_name,
		industry_code,
		business_code,
		round(rand()*5) talents_type,  --没有这个列
		date_format(dt,'yyyy-MM') as alter_time
	from dwd_enterprise_person_info
),


tmp_business_alive2 as
(
select
	industry_code,
	business_code,
	alter_time as operate_time,
	count(1) as high_tech_talent_num
from tmp_business_alive1
where talents_type = '0'  --高科技人才
group by industry_code,business_code,alter_time
),


tmp_business_alive3_1 as--人才需求总量
(
select
  industry_code,
  business_code,
  ent_name,
  posts_name,
  posts,--当月每个公司每个招聘职位最后日期还在招聘的人数
  mon_time
from (
  select
    industry_code,
    business_code,
    ent_name,
    posts_name,
    posts,
    update_time,
    mon_time,
    row_number() over(partition by industry_code,business_code,ent_name,posts_name,mon_time order by update_time desc) as rn
  from (
    select
      ent_name,
      industry_code,
      business_code,
      posts_name,
      posts,
      update_time,
      date_format(update_time,'yyyy-MM') as mon_time
    from dwd_enterprise_position_require_detail
    )a
  )aa
where rn =1 --取出最近的日期
),


tmp_business_alive3_2 as
(
select
	industry_code,
	business_code,
	mon_time as operate_time,
	sum(posts) as require_talent_num
from tmp_business_alive3_1
group by industry_code,business_code,mon_time

),


tmp_business_alive4 as
(
select
	industry_code,
	business_code,
	ew_time as operate_time,
	count(1) as entry_sum --已就业人数
from tmp_business_alive1
group by industry_code,business_code,ew_time

),


tmp_business_alive5 as
(
select
	industry_code,
	business_code,
	ow_time as operate_time,
	count(1) as quit_num  --已离职人数
from tmp_business_alive1
where ow_time is not null
group by industry_code,business_code,ow_time
),


tmp_business_alive6 as
(
select
	industry_code,
	business_code,
	operate_time,
	sum(high_tech_talent_num) as high_tech_talent_num1,
	sum(require_talent_num) as require_talent_num1,
	sum(entry_sum) as entry_sum1,
	sum(quit_num) as quit_num1
from (
	select
		industry_code,
		business_code,
		operate_time,
		high_tech_talent_num,
		0 require_talent_num,
		0 entry_sum,
		0 quit_num
	from tmp_business_alive2


	union all
	select
		industry_code,
		business_code,
		operate_time,
		0 high_tech_talent_num,
		require_talent_num,
		0 entry_sum,
		0 quit_num
	from tmp_business_alive3_2

	union all
	select
		industry_code,
		business_code,
		operate_time,
		0 high_tech_talent_num,
		0 require_talent_num,
		entry_sum,
		0 quit_num
	from tmp_business_alive4

	union all
	select
		industry_code,
		business_code,
		operate_time,
		0 high_tech_talent_num,
		0 require_talent_num,
		0 entry_sum,
		quit_num
	from tmp_business_alive5
	)a
group by industry_code,business_code,operate_time
)

insert into table cy_database.dws_business_alive
select
	industry_code,
	business_code,
	high_tech_talent_num1,
	require_talent_num1,
	entry_sum1,
	quit_num1,
	operate_time
from tmp_business_alive6

;
"
$hive -e "$sql"